#! /bin/bash
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi
echo '========================================'
echo '==============全量导入==============='
echo '========================================'
/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
-- 需求1：每天/每月/每年各个校区的报名人数
delete from hive.bxg_rpt.school_signup;
insert into hive.bxg_rpt.school_signup
select
"year",
year_month,
year_month_day,
school_name,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='school'
union all
select
"year",
year_month,
null as year_month_day,
school_name,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='school'
union all
select
"year",
null as year_month,
null as year_month_day,
school_name,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='school';

-- 需求1.1：每天各个校区的报名人数  ok
delete from bxg_rpt.day_school_signup;
insert into bxg_rpt.day_school_signup
select
year_month_day,
school_name,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='school';
-- 需求1.2：每月各个校区的报名人数  ok
insert into bxg_rpt.month_school_signup
select
year_month,
school_name,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='school';
-- 需求1.3：每年各个校区的报名人数 ok
delete from bxg_rpt.year_school_signup;
insert into bxg_rpt.year_school_signup
select
"year",
school_name,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='school';


-- 需求2：线上线下各个校区的报名人数 ok
delete from bxg_rpt.origintype_school_signup;
insert into bxg_rpt.origintype_school_signup
select
"year",
year_month,
year_month_day,
school_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='school+origintype'
union all
select
"year",
year_month,
null as year_month_day,
school_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='school+origintype'
union all
select
"year",
null as year_month,
null as year_month_day,
school_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='school+origintype';

-- 需求2.1：每天线上线下各个校区的报名人数 ok
delete from bxg_rpt.day_origintype_school_signup;
insert into bxg_rpt.day_origintype_school_signup
select
year_month_day,
school_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='school+origintype';

-- 需求2.2：每月线上线下各个校区的报名人数
delete from bxg_rpt.month_origintype_school_signup;
insert into bxg_rpt.month_origintype_school_signup
select
year_month,
school_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='school+origintype';

-- 需求2.3：每年线上线下各个校区的报名人数
delete from bxg_rpt.year_origintype_school_signup;
insert into bxg_rpt.year_origintype_school_signup
select
"year",
school_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='school+origintype';

-- 需求6：线上线下各个咨询中心的报名人数
delete from bxg_rpt.origintype_department_signup;
insert into bxg_rpt.origintype_department_signup
select
"year",
year_month,
year_month_day,
department_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='origintype+department'
union all
select
"year",
year_month,
null as year_month_day,
department_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='origintype+department'
union all
select
"year",
null as year_month,
null as year_month_day,
department_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='origintype+department';
-- 需求6.1：每天线上线下各个咨询中心的报名人数
delete from bxg_rpt.day_origintype_department_signup;
insert into bxg_rpt.day_origintype_department_signup
select
year_month_day,
department_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='origintype+department';

-- 需求6.2：每月线上线下各个咨询中心的报名人数
delete from bxg_rpt.month_origintype_department_signup;
insert into bxg_rpt.month_origintype_department_signup
select
year_month,
department_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='origintype+department';

-- 需求6.3：每年线上线下各个咨询中心的报名人数
delete from bxg_rpt.year_origintype_department_signup;
insert into bxg_rpt.year_origintype_department_signup
select
"year",
department_name,
origin_type,
signup_count
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='origintype+department';

-- 需求7：线上线下的意向转报名率 = 报名人数 / 意向人数
delete from bxg_rpt.origintype_signup_relationship_ratio;
insert into bxg_rpt.origintype_signup_relationship_ratio
select
"year",
year_month,
year_month_day,
origin_type,
cast(cast(signup_count as decimal(34,4)) / cast(relationship_count as decimal(34,4)) * 100  as decimal(5,2))  as signup_relationship_ratio
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='origintype'
union all
select
"year",
year_month,
null as year_month_day,
origin_type,
cast(cast(signup_count as decimal(34,4)) / cast(relationship_count as decimal(34,4)) * 100  as decimal(5,2))  as signup_relationship_ratio
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='origintype'
union all
select
"year",
null as ear_month,
null as year_month_day,
origin_type,
cast(cast(signup_count as decimal(34,4)) / cast(relationship_count as decimal(34,4)) * 100  as decimal(5,2))  as signup_relationship_ratio
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='origintype';
-- 需求7.1：每天线上线下的意向转报名率 = 报名人数 / 意向人数
delete from bxg_rpt.day_origintype_signup_relationship_ratio;
insert into bxg_rpt.day_origintype_signup_relationship_ratio
select
year_month_day,
origin_type,
cast(cast(signup_count as decimal(34,4)) / cast(relationship_count as decimal(34,4)) * 100  as decimal(5,2))  as signup_relationship_ratio
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='origintype';

-- 需求7.2：每月线上线下的意向转报名率 = 报名人数 / 意向人数
delete from bxg_rpt.month_origintype_signup_relationship_ratio;
insert into bxg_rpt.month_origintype_signup_relationship_ratio
select
year_month,
origin_type,
cast(cast(signup_count as decimal(34,4)) / cast(relationship_count as decimal(34,4)) * 100  as decimal(5,2))  as signup_relationship_ratio
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='origintype';

-- 需求7.3：每年线上线下的意向转报名率 = 报名人数 / 意向人数
delete from bxg_rpt.year_origintype_signup_relationship_ratio;
insert into bxg_rpt.year_origintype_signup_relationship_ratio
select
"year",
origin_type,
cast(cast(signup_count as decimal(34,4)) / cast(relationship_count as decimal(34,4)) * 100  as decimal(5,2))  as signup_relationship_ratio
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='origintype';

-- 需求8：线上的有效线索报名转换率 = 报名人数 / 有效线索人数
delete from bxg_rpt.origintype_signup_clue_ratio;
insert into bxg_rpt.origintype_signup_clue_ratio
select
"year",
year_month,
year_month_day,
origin_type,
if(valid_clue_count=0,0,cast(cast(signup_count as decimal(34,4)) / cast(valid_clue_count as decimal(34,4)) * 100 as decimal(5,2)))  as signup_clue_ratio
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='origintype'
and origin_type='ONLINE'
union all
select
"year",
year_month,
null as year_month_day,
origin_type,
if(valid_clue_count=0,0,cast(cast(signup_count as decimal(34,4)) / cast(valid_clue_count as decimal(34,4)) * 100 as decimal(5,2)))  as signup_clue_ratio
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='origintype' and origin_type='ONLINE'
union all
select
"year",
null as year_month,
null as year_month_day,
origin_type,
if(valid_clue_count=0,0,cast(cast(signup_count as decimal(34,4)) / cast(valid_clue_count as decimal(34,4)) * 100 as decimal(5,2)))  as signup_clue_ratio
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='origintype' and origin_type='ONLINE';

-- 需求8.1：每天线上的有效线索报名转换率 = 报名人数 / 有效线索人数
delete from bxg_rpt.day_origintype_signup_clue_ratio;
insert into bxg_rpt.day_origintype_signup_clue_ratio
select
year_month_day,
origin_type,
if(valid_clue_count=0,0,cast(cast(signup_count as decimal(34,4)) / cast(valid_clue_count as decimal(34,4)) * 100 as decimal(5,2)))  as signup_clue_ratio
from
hive.bxg_dm.dm_signup
where time_type='day' and group_type='origintype'
and origin_type='ONLINE';

-- 需求8.2：每月线上的有效线索报名转换率 = 报名人数 / 有效线索人数
delete from bxg_rpt.month_origintype_signup_clue_ratio;
insert into bxg_rpt.month_origintype_signup_clue_ratio
select
year_month,
origin_type,
if(valid_clue_count=0,0,cast(cast(signup_count as decimal(34,4)) / cast(valid_clue_count as decimal(34,4)) * 100 as decimal(5,2)))  as signup_clue_ratio
from
hive.bxg_dm.dm_signup
where time_type='month' and group_type='origintype' and origin_type='ONLINE';

-- 需求8.3：每年线上的有效线索报名转换率 = 报名人数 / 有效线索人数
delete from bxg_rpt.year_origintype_signup_clue_ratio;
insert into bxg_rpt.year_origintype_signup_clue_ratio
select
"year",
origin_type,
if(valid_clue_count=0,0,cast(cast(signup_count as decimal(34,4)) / cast(valid_clue_count as decimal(34,4)) * 100 as decimal(5,2)))  as signup_clue_ratio
from
hive.bxg_dm.dm_signup
where time_type='year' and group_type='origintype' and origin_type='ONLINE';"